package com.uinnova.product.eam.web.util;

import java.io.InputStream;
import java.util.*;

import com.binary.tools.excel.Column;
import com.binary.tools.excel.ExcelStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.binary.core.exception.MessageException;
import com.binary.core.util.BinaryUtils;
import com.uinnova.product.eam.web.diagram.bean.ExcelDataMessage;
import com.uinnova.product.eam.web.diagram.bean.VcExcelLinked;
import com.uinnova.product.eam.web.integration.bean.VcCiClassInfo;
import com.uinnova.product.vmdb.comm.model.ci.CcFixAttrMapping;

public class ExcelParseUtil {

	public static List<String> parseCiExcel2CiCode(Workbook wb,List<VcCiClassInfo> classInfos){
		
		if(BinaryUtils.isEmpty(wb) || BinaryUtils.isEmpty(classInfos)) return null;
		Map<String,CcFixAttrMapping> classStdCodeFixMappingMap = new HashMap<String,CcFixAttrMapping>();
		for(VcCiClassInfo info : classInfos) {
			classStdCodeFixMappingMap.put(info.getCiClass().getClassCode(), info.getFixMapping());
		}
		
		List<String> ciCodes = new ArrayList<String>();
		int sheetNum = wb.getNumberOfSheets();
		for(int i = 0; i<sheetNum ;i++){
			String sheetName = wb.getSheetName(i).trim();
			CcFixAttrMapping mapping = classStdCodeFixMappingMap.get(sheetName);
			String nmCiCode = mapping.getNmCiCode();
			Sheet sheet = wb.getSheet(sheetName);
			int ciCodeCellNum = 0;
			Row firstRow  = sheet.getRow(0); //获取第一行的
			for(Cell cell : firstRow){
				String value = getStringCellValue(cell);
				if(value.equals(nmCiCode)) {
					ciCodeCellNum = cell.getColumnIndex();
					break;
				}
			}
			for(Row row : sheet) {
				int rowNum = row.getRowNum();
				String value = getStringCellValue(row.getCell(ciCodeCellNum));
				if(!BinaryUtils.isEmpty(value) && rowNum != 0) ciCodes.add(value);
			}
		}
		return ciCodes;
	}

	public static Map<String,Map<Integer,List<ExcelDataMessage>>> parseExcelBySheetRow(Workbook wb){
		List<ExcelDataMessage> excelDataMessages = parseExcel(wb);
		Map<String,Map<Integer,List<ExcelDataMessage>>> res = new HashMap<>();
		if(BinaryUtils.isEmpty(excelDataMessages)) return res;
		Map<String, List<ExcelDataMessage>> sheetNameValuesMap = BinaryUtils.toObjectGroupMap(excelDataMessages,"sheetName");
		for(Map.Entry<String, List<ExcelDataMessage>> entry : sheetNameValuesMap.entrySet()){
			String sheetName = entry.getKey();
			List<ExcelDataMessage> value = entry.getValue();
			Map<Integer, List<ExcelDataMessage>> rowNumValuesMap = BinaryUtils.toObjectGroupMap(value, "rowNum");
			res.put(sheetName,rowNumValuesMap);
		}
		return res;
	}
	
	public static List<ExcelDataMessage> parseExcel(Workbook wb){
		
		if(BinaryUtils.isEmpty(wb)) return Collections.emptyList();
		
		List<ExcelDataMessage> result = new ArrayList<ExcelDataMessage>();
		Set<String> values = new HashSet<String>();
		int sheetNum = wb.getNumberOfSheets();
		for(int i = 0; i<sheetNum ;i++){
			String sheetName = wb.getSheetName(i).trim();
			Sheet sheet = wb.getSheet(sheetName);
			for(Row row : sheet){
				for(Cell cell : row){
					String value = getStringCellValue(cell);
					if(BinaryUtils.isEmpty(value) || values.contains(value)) continue;
					
					ExcelDataMessage excelData = new ExcelDataMessage();
					
					int rowNum = cell.getRowIndex();
					int colNum = cell.getColumnIndex();
					
					excelData.setRowNum(rowNum+1);
					excelData.setColNum(colNum+1);
					excelData.setSheetName(sheetName);
					excelData.setValue(value);
					
					result.add(excelData);
					
				}
			}
		}
		return result;
	}
	
	public static List<VcExcelLinked> parseExcel2Arrays(Workbook wb){

		if(BinaryUtils.isEmpty(wb)) return Collections.emptyList();
		
		List<VcExcelLinked> result = new ArrayList<VcExcelLinked>();
		int sheetNum = wb.getNumberOfSheets();
		for(int i = 0; i<sheetNum ;i++){
			String sheetName = wb.getSheetName(i).trim();
			Sheet sheet = wb.getSheet(sheetName);
			int j = 0;
			for(Row row : sheet){
				
				if(j != 0){
					VcExcelLinked excelLinked = new VcExcelLinked();
					List<String> elements = new ArrayList<String>();
					
					Cell linkedNameCell = row.getCell(0);
					Cell elementsCell = row.getCell(1);
					Cell separationCell = row.getCell(2);
					
					String separation = ",";
					String separationValue = getStringCellValue(separationCell);
					if(!BinaryUtils.isEmpty(separationValue)) separation = separationValue;
					
					String linkedName = getStringCellValue(linkedNameCell);
					
					String elementsStr = getStringCellValue(elementsCell);
					if(!BinaryUtils.isEmpty(elementsStr) && !BinaryUtils.isEmpty(linkedName)){
						String[] strs = elementsStr.split(separation);
						elements = Arrays.asList(strs);
						excelLinked.setLinkedName(linkedName);
						excelLinked.setElements(elements);
						result.add(excelLinked);
					}
					
				}
				j++;
			}
		}
		return result;
	}
	
	public static Integer getSheetNum(Workbook wb){
		Integer sheetNum = 0;
		if(!BinaryUtils.isEmpty(wb)) sheetNum = wb.getNumberOfSheets();
		return sheetNum;
	}

	public static Workbook getWorkBook(InputStream input,String excelName){
		if(BinaryUtils.isEmpty(input) || BinaryUtils.isEmpty(excelName)) return null;
		Workbook wb = null;
		try {
			if(excelName.endsWith(".xls"))  wb = new HSSFWorkbook(input);
			if(excelName.endsWith(".xlsx")) wb = new XSSFWorkbook(input);
		} catch (Exception e) {
			//请上传正确的excel文件！
			throw MessageException.i18n("DMV_FILE_NOT_CORRECT");
		}
		return wb;
	}
	
	  /**
     * 获取单元格数据内容为字符串类型的数据
     * 
     * @param cell Excel单元格
     * @return String 单元格数据内容
     */
    @SuppressWarnings("deprecation")
	private static String getStringCellValue(Cell cell) {
        String strCell = "";
        if(cell == null) return strCell;

        switch (cell.getCellType()) {
        case STRING:
            strCell = cell.getStringCellValue();
            break;
			case NUMERIC:
            strCell = String.valueOf(cell.getNumericCellValue());
            break;
			case BOOLEAN:
            strCell = String.valueOf(cell.getBooleanCellValue());
            break;
			case FORMULA:
        	strCell = "";
             break;
			case BLANK:
            strCell = "";
            break;
        default:
            strCell = "";
            break;
        }
        if (strCell.equals("") || strCell == null) {
            return "";
        }
        return strCell;
    }


	public static Column getColumn(String string) {
		Integer isRequired = 2;
		short color = 0;
		Integer m = 1;
		if (m.equals(isRequired)) {
			color = HSSFColor.HSSFColorPredefined.RED.getIndex();
		} else {
			color = HSSFColor.HSSFColorPredefined.BLACK.getIndex();
		}

		return new Column(string, string, 0, Column.DefaultAlign, color);
	}


	public static ExcelStyle getTitleStyle(List<String> list) {
		Column[] columns = new Column[list.size()];
		int size = list.size();

		for(int i=0;i<size;i++){
			columns[i] = getColumn(list.get(i));
		}

		return new ExcelStyle(columns);
	}
}
